package metier;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DB.Request;
import bean.Repertoires;

public class RepertoiresMetier {

	private static RepertoiresMetier singleton = new RepertoiresMetier();

	
	public static RepertoiresMetier getInstance(){
		return singleton;
	}
	
	public Repertoires save(Repertoires repertoire){
		if(repertoire != null){
			String query;
			if (repertoire.getId_repertoire()!= null) {
				//UPDATE
				query = "UPDATE repertoires SET adresse=?,libelle=?,type=?,id_classe=?,id_matiere=?,id_apprenti = ? WHERE id_repertoire="+repertoire.getId_repertoire();
			} else {
				//INSERT
				query = "INSERT INTO repertoires(adresse,libelle,type,id_classe,id_matiere,id_apprenti) VALUES (?,?,?,?,?,?)";
			}
			List<Object> param = new ArrayList<Object>();
			param.add(repertoire.getAdresse());
			param.add(repertoire.getLibelle());
			param.add(repertoire.getType());
			if(repertoire.getClasse() != null){
				param.add(repertoire.getClasse().getId_classe());
			} else {
				param.add(null);
			}
			if(repertoire.getMatiere() != null){
				param.add(repertoire.getMatiere().getId_matiere());
			} else{
				param.add(null);
			}
			if(repertoire.getApprenti() != null){
				param.add(repertoire.getApprenti().getId_apprenti());
			} else {
				param.add(null);
			}
			Request.getInstance().executeUpdate(query, param);
		}
		if (repertoire.getId_repertoire() != null) {
			return getById(repertoire.getId_repertoire());
		} else {
			return getById(getLastId());
		}
	}
	
	public void delete(Repertoires repertoire){
		String query = "DELETE FROM repertoires WHERE id_repertoire=" + repertoire.getId_repertoire();
		Request.getInstance().executeUpdate(query);
	}
	
	public Repertoires getById(Integer id){
		Repertoires repertoire = null;
		String query = "SELECT * FROM repertoires WHERE id_repertoire=" + id;
		ResultSet resultat = Request.getInstance().executeQuery(query);
		try {
			while(resultat.next()){
				repertoire = new Repertoires(id, resultat.getString("adresse"), resultat.getString("libelle"),resultat.getString("type"),ClasseMetier.getInstance().getById(resultat.getInt("id_classe")),MatiereMetier.getInstance().getById(resultat.getInt("id_matiere")),ApprentiMetier.getInstance().getById(resultat.getInt("id_apprenti")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return repertoire;
	}
	
	public List<Repertoires> getAll(){
		List<Repertoires> repertoires = new ArrayList<Repertoires>();
		String query = "SELECT * FROM repertoires";
		ResultSet resultat = Request.getInstance().executeQuery(query);
		try {
			while(resultat.next()){
				Repertoires repertoire = new Repertoires(resultat.getInt("id_repertoire"), resultat.getString("adresse"), resultat.getString("libelle"),resultat.getString("type"),ClasseMetier.getInstance().getById(resultat.getInt("id_classe")),MatiereMetier.getInstance().getById(resultat.getInt("id_matiere")),ApprentiMetier.getInstance().getById(resultat.getInt("id_apprenti")));
				repertoires.add(repertoire);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return repertoires;
	}

	
	private Integer getLastId(){
		Integer result = null;
		String query = "SELECT max(id_repertoire) AS id_repertoire FROM repertoires";
		ResultSet resultat = Request.getInstance().executeQuery(query);
		try {
			while(resultat.next()){
				result = resultat.getInt("id_repertoire");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<Repertoires> recherche(String type, Integer idClasse, Integer idMatiere, Integer idApprenti, String order){
		List<Repertoires> repertoires = new ArrayList<Repertoires>();
		StringBuffer query = new StringBuffer("SELECT * FROM repertoires WHERE adresse IS NOT NULL");
		if(type != null){
			query.append(" AND type = ").append("'").append(type).append("'");
		}
		if(idClasse != null){
			query.append(" AND id_classe = ").append("'").append(idClasse).append("'");
		}
		if(idMatiere != null){
			query.append(" AND id_matiere = ").append("'").append(idMatiere).append("'");
		}
		if(idApprenti != null){
			query.append(" AND id_apprenti = ").append("'").append(idApprenti).append("'");
		}
		if(order != null){
			query.append(" ORDER BY ").append(order);
		}
		ResultSet resultat = Request.getInstance().executeQuery(query.toString());
		try {
			while(resultat.next()){
				Repertoires repertoire = new Repertoires(resultat.getInt("id_repertoire"), resultat.getString("adresse"), resultat.getString("libelle"),resultat.getString("type"),ClasseMetier.getInstance().getById(resultat.getInt("id_classe")),MatiereMetier.getInstance().getById(resultat.getInt("id_matiere")),ApprentiMetier.getInstance().getById(resultat.getInt("id_apprenti")));
				repertoires.add(repertoire);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return repertoires;
	}
}
